
UPDATE [trladmin].[dbo].trl_members
SET m_type = 'f'
WHERE [m_phone] LIKE '%family%'

UPDATE [trladmin].[dbo].[trl_members]
SET m_notes = m_notes + ' ' + m_phone
WHERE 
	ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([m_phone],'-',''),'.',''),'family',''),' ',''),')',''),'(','')) = 0
	AND m_phone NOT IN ('not given','n/g','none given','family','family not given','not known','none','??','no','not give','not')

DELETE FROM [trladmin].[dbo].[Memberships]
GO


INSERT INTO [trladmin].[dbo].[Memberships]
           ([membershipType]
           ,[membershipExpiration]
           ,[firstName]
           ,[lastname]
           ,[address]
           ,[city]
           ,[state]
           ,[postalCode]
           ,[phoneNumber]
           ,[email]
           ,[forumUsername]
           ,[notes]
           ,[memberSince]
           ,[sendPaperNewsletter]
           ,[sendENewsletter]
           ,[okayToContact]
           ,welcomeKitLastSentOn
           ,welcomeKitLastSentByFullName
           ,needWelcomeKit
           )
SELECT 
      CASE WHEN ([m_type] = 'i' OR [m_type] IS NULL) THEN 1 ELSE 2 END as type
      ,[m_expire]
      ,CAST([m_fname] AS VARCHAR(100))
      ,CAST([m_lname] AS VARCHAR(100))
      ,CAST([m_address] AS VARCHAR(100))
      ,CAST([m_city] AS VARCHAR(100))
      ,CAST([m_state] AS CHAR(2))
      ,CAST([m_zip] AS VARCHAR(9))
      ,CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([m_phone],'-',''),'.',''),'family',''),' ',''),')',''),'(','')) = 1 THEN 
		REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([m_phone],'-',''),'.',''),'family',''),' ',''),')',''),'(','') 
		ELSE NULL
		END as phone
      ,[m_email]
      ,[m_forumusername]
      ,[m_notes]
      ,[m_since]
      ,CASE WHEN [m_newsletter] = 'y'  OR [m_newsletter] IS NULL THEN 1 ELSE 0 END as paper
      ,CASE WHEN [m_eNewsletter] = 'y' OR [m_eNewsletter] IS NULL THEN 1 ELSE 0 END as eNewsletter
      ,CASE WHEN [m_contact] = 'y' OR [m_contact] IS NULL THEN 1 ELSE 0 END as okayToContact
      ,m_memKitSentOn
      ,(SELECT U_FNAME + ' ' + U_LNAME FROM Trl_users
        WHERE U_UID = m_memKitSentBy),
        ISNULL(m_needMemKit,0)
  FROM [trladmin].[dbo].[trl_members]





/*
TODO:
Data still need to copy over:
           --,[secondFirstName]
           --,[secondLastName]
           --,[secondEmail]

*/

/*GO

SELECT * FROM MembershipTypes

*/
